import { Service } from 'egg';
import { User, UserRole, UserStatus } from '../../model/user';

const SQL_FIND_USER_BY_ID = `SELECT * FROM user WHERE id = ? AND status != ${UserStatus.Error}`;
const SQL_FIND_USER_BY_OPENID = `SELECT * FROM user WHERE openid = ? AND status != ${UserStatus.Error}`;
const SQL_FIND_USER_BY_PHONE = `SELECT * FROM user WHERE status != ${UserStatus.Error} AND phone = ?`;
const SQL_FIND_USER_BY_PASSWORD = `SELECT * FROM user WHERE status != ${UserStatus.Error}
                                 AND username = ? AND password = ?`;
const SQL_GET_USERS_BY_ROLE_PAGE = `SELECT 
                                        u.id as id,
                                        u.username as username,
                                        u.password as password,
                                        u.avatar as avatar,
                                        u.created_at as created_at,
                                        u.updated_at as updated_at,
                                        u.role as role,
                                        u.status as status,
                                        u.openid as openid,
                                        u.phone as phone,
                                        u.teacher_id as teacher_id,
                                        tu.username as teacher_name,
                                        u.admin_id as admin_id,
                                        au.username as admin_name
                                    FROM user as u
                                    LEFT JOIN  user as au ON au.id = u.admin_id
                                    LEFT JOIN user as tu ON tu.id = u.teacher_id
                                    WHERE u.role = ? LIMIT ? OFFSET ?`;
const SQL_COUNT_USERS = `SELECT count(*) FROM user WHERE role = ? AND status != ${UserStatus.Error}`;

const SQL_SEARCH_USER =
    `SELECT * FROM user WHERE status != -1 AND role = ? AND (username LIKE ? OR phone LIKE ?) ORDER BY status DESC;`;

const SQL_SEARCH_USERS_AND_TEACHERS =
    `SELECT * FROM user
    WHERE status != -1 AND (role = 2 OR role = 3) AND (username LIKE ? OR phone LIKE ?)
    ORDER BY status DESC;`;

export default class DbUserService extends Service {

    public async findAdminId(id: number): Promise<number|null> {
        const user = await this.app.mysql.get('user', { id });
        return user ? user.admin_id : null;
    }

    public async search(role: number, keyword: string) {
        const mysql = this.app.mysql;
        keyword = `%${keyword}%`;
        return await mysql.query(SQL_SEARCH_USER, [role, keyword, keyword]);
    }

    public async searchUsersAndTeachers(keyword: string) {
        const mysql = this.app.mysql;
        keyword = `%${keyword}%`;
        return await mysql.query(SQL_SEARCH_USERS_AND_TEACHERS, [keyword, keyword]);
    }

    public async findOneByOpenid(openid: string): Promise<User|null> {
        const mysql = this.app.mysql;
        const user = await mysql.queryOne(SQL_FIND_USER_BY_OPENID, [openid]);
        return this.toJson(user);
    }

    public async findOneById(id: number): Promise<User|null> {
        const mysql = this.app.mysql;
        const user = await mysql.queryOne(SQL_FIND_USER_BY_ID, [id]);
        return this.toJson(user);
    }

    public async findOneByPassword(username: string, password: string) {
        const mysql = this.app.mysql;
        const user = await mysql.queryOne(SQL_FIND_USER_BY_PASSWORD, [username, password]);
        return this.toJson(user);
    }

    public async getUserPhone(userId: number) {
        const mysql = this.app.mysql;
        const result = await mysql.select('user', {
            where: {
                user_id: userId,
            },
        });
        if (result) {
            return result.phone;
        }
        return null;
    }

    public async findOneByPhone(phone: string): Promise<User> {
        const mysql = this.app.mysql;
        const user = await mysql.queryOne(SQL_FIND_USER_BY_PHONE, [phone]);
        return user as User;
    }

    public async getUsersCount(role: UserRole): Promise<number> {
        const mysql = this.app.mysql;
        const count = await mysql.queryOne(SQL_COUNT_USERS, [ role ]);
        return count;
    }

    public async createByUser(openid: string, username: string, phone: string, mysql?: any) {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const result = await mysql.insert('user', {
            openid,
            username,
            phone,
            role: UserRole.Student,
            status: UserStatus.NeedConfirm, // 默认注册的用户状态是需要绑定手机号
            created_at: mysql.literals.now,
            updated_at: mysql.literals.now,
        });
        if (result.affectedRows !== 1) {
            this.ctx.helper.dbError();
        }
        this.service.db.msg.create(1, '有新用户注册需要确认用户信息');
    }

    public async bindOpenid(user: User, openid: string) {
        const mysql = this.app.mysql; // 初始化事务
        await mysql.update('user', {
            openid,
            updated_at: mysql.literals.now,
            status: UserStatus.Normal,
        }, {
            where: {
                id: user.id,
            },
        });  // 绑定openid

        return await this.findOneById(user.id);
    }

    public async updateUserStatus(userId: number, status: UserStatus) {
        const options = {
            where: {
                id: userId,
            },
        };
        const newData = {
            status,
        };
        // 更新用户状态
        await this.app.mysql.update('user', newData, options);
    }

    public async getUserName(userId: number): Promise<string> {
        const user = await this.app.mysql.get('user', { id: userId });
        if (!user) {
            return '';
        }
        return user.username;
    }

    public async updateUsername(id: number, username: string) {
        await this.app.mysql.update('user', { username }, { where: { id }});
        return this.findOneById(id);
    }

    public async getUsersByPage(role: UserRole, pageNum: number, pageSize: number) {
        const mysql = this.app.mysql;
        return await mysql.query(SQL_GET_USERS_BY_ROLE_PAGE, [role, pageSize, (pageNum - 1) * pageSize]);
    }

    public async addUser(username: string, phone: string,
        role: UserRole, password: string|undefined|null, adminId: number,
        mysql?: any): Promise<number> {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const result = await mysql.insert('user', {
            username,
            status: UserStatus.Normal,
            phone,
            role,
            password,
            admin_id: adminId,
            created_at: mysql.literals.now,
            updated_at: mysql.literals.now,
        });
        return result.insertId;
    }

    public async deleteUserById(id: number, mysql?: any): Promise<boolean> {
        if (!mysql) {
            mysql = this.app.mysql;
        }
        const result = await mysql.delete('user', { id });
        if (result.affectedRows === 1) {
            return true;
        }
        return false;
    }

    public async updateUser(id: number, newUser: any, mysql?: any) {
        if (!mysql ) {
            mysql = this.app.mysql;
        }
        await mysql.update('user', {
            ...newUser,
            updated_at: mysql.literals.now,
        }, { where: { id } });
        return await this.findOneById(id);
    }

    private toJson(user: any) {
        if (!user) {
            return null;
        }
        return {
            id: user.id,
            openid: user.openid,
            username: user.username,
            avatar: user.avatar,
            phone: user.phone,
            role: user.role,
            teacher_id: user.teacher_id,
            status: user.status,
            admin_id: user.admin_id,
        };
    }
}
